import datetime

def FormatDateArrayToString(DateArray):
    ##[(Date, Percent),(Date, Percent)]
    
    ##output = ##year,month,day#percentage##year,month,day#percentage##
    Str = """##"""
    i = 0
    while i < len(DateArray):
        Str = Str + """%d,%d,%d#%d##"""%(DateArray[i][0].year, DateArray[i][0].month, DateArray[i][0].day, DateArray[i][1])
        i= i + 1
    return Str

def GetDateArrayFromString(String):
    Dates = String.split("""##""")[1:-1]
    DateArray = []
    i = 0
    while i < len(Dates):
        StrDate = Dates[i].split("""#""")[0].split(""",""") 
        D = datetime.date(int(StrDate[0]),int(StrDate[1]),int(StrDate[2]))
        DateArray.append((D, int(Dates[i].split("""#""")[1])))
        i = i + 1
    return DateArray

class PeopleOnEvents:
    def __init__(self, SQLConn, dBName = "PEOPLE_ON_EVENTS"):
        self.SQLConn = SQLConn
        self.PeopleOnEventsTableName = dBName
        
    def AddPersonToEvent(self, Person_ID, EventID, Dates):
        self.SQLConn.SendCommand("""
         INSERT INTO %s (Eve_id, Peo_id, DatesOnEvent)
         VALUES (%d, %d, '%s')
       """ %(self.PeopleOnEventsTableName, EventID, Person_ID, FormatDateArrayToString(Dates)))
    
    def RemovePersonFromEvent(self, EID, PID):
        self.SQLConn.SendCommand("""    
         DELETE FROM %s where Eve_id=%d AND Peo_id=%d
         """ % (self.PeopleOnEventsTableName, EID,PID))
        
    def RemovePerson(self, PID):
        self.SQLConn.SendCommand("""    
         DELETE FROM %s where Peo_id=%d
         """ % (self.PeopleOnEventsTableName, PID))
        
    def RemoveEvent(self, EID):
        self.SQLConn.SendCommand("""    
         DELETE FROM %s where Eve_id=%d
         """ % (self.PeopleOnEventsTableName, EID))
        
    def GetPIDSOnEvent(self, EID):
        self.SQLConn.SendCommand("""SELECT Peo_id FROM %s WHERE Eve_id = %d"""%(self.PeopleOnEventsTableName,EID))
        rows = self.SQLConn.FetchAll ()
        People = []
        for Person in rows:
            People.append(Person[0])
        return People
        
    def GetAllPeopleOnEvent(self, EID):
        self.SQLConn.SendCommand("""SELECT Peo_id, DatesOnEvent FROM %s WHERE Eve_id = %d"""%(self.PeopleOnEventsTableName,EID))
        rows = self.SQLConn.FetchAll ()
        People = []
        for Person in rows:
            People.append([Person[0], GetDateArrayFromString(Person[1])])
        return People
    
    def GetAllPeopleOnEventDict(self, EID):
        self.SQLConn.SendCommand("""SELECT Peo_id, DatesOnEvent FROM %s WHERE Eve_id = %d"""%(self.PeopleOnEventsTableName,EID))
        rows = self.SQLConn.FetchAll ()
        People = {}
        for Person in rows:
            People[Person[0]] = [Person[0], GetDateArrayFromString(Person[1])]
        return People
    
    
    def GetAllEventsPersonOn(self, PID):
        self.SQLConn.SendCommand("""SELECT Eve_id, DatesOnEvent FROM %s WHERE Peo_id = %d"""%(self.PeopleOnEventsTableName,PID))
        rows = self.SQLConn.FetchAll ()
        Events = []
        for Event in rows:
            Events.append([Event[0], GetDateArrayFromString(Event[1])])
        return Events
    
    def GetDatesPersonOnEvent(self, EID, PID):
        self.SQLConn.SendCommand("""SELECT DatesOnEvent FROM %s WHERE Peo_id = %d AND Eve_id = %d"""%(self.PeopleOnEventsTableName, PID,EID))
        rows = self.SQLConn.FetchAll ()
        return GetDateArrayFromString(rows[0][0])
        
        
        
    
    def UpdateEventPeople(self, EID, PID, Dates):
       self.SQLConn.SendCommand("""UPDATE %s
                                SET DatesOnEvent = '%s'
                                WHERE Peo_id = %d
                                AND Eve_id = %d
                                """ % (self.PeopleOnEventsTableName, FormatDateArrayToString(Dates), PID, EID))
       
    def GetPersonsManDays(self, EID = -1, PID = -1, Dates = ''):
        if Dates == '':
            Dates= self.GetDatesPersonOnEvent(EID, PID)
        
        ManDays = 0
        for Date in Dates:
            ManDays = ManDays + (1.0 * (Date[1]/100.0))
        
        return ManDays

    def GetTotalEventManDays(self, EID):
        PeopleOnEvent= self.GetAllPeopleOnEvent(EID)
        ManDays = 0
        for PID, Dates in PeopleOnEvent:
            for Date in Dates:
                ManDays = ManDays + (1.0 * (Date[1]/100.0))
        
        return ManDays
        
       
    
def GenerateTables(SQL):
    print """Generating "PEOPLE_ON_EVENTS" Table"""
    
    SQL.SendCommand ("DROP TABLE IF EXISTS PEOPLE_ON_EVENTS")
    SQL.SendCommand("""CREATE TABLE PEOPLE_ON_EVENTS(
                    Eve_id int,
                    Peo_id int,
                    DatesOnEvent varchar(2000))
                    """)